use ppjdb;
go


if not exists (select 1 from [dbo].[tblUser] (nolock) where UserEmail = 'admin@ppj.com')
insert into [dbo].[tblUser](
	[StoreID],
	[FirstName],
	[LastName],
	[UserEmail],
	[Password],
	[SecureQuestion],
	[SecureAnswer])
values (	
	1,
	'ppj',
	'admin',
	'admin@ppj.com',
	'test',
	'test',
	'test')
go

-------------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblUserMenu_AppMenuID]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblUserMenu]'))
ALTER TABLE [dbo].[tblUserMenu] DROP CONSTRAINT [FK_tblUserMenu_AppMenuID]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblUserMenu]') AND type in (N'U'))
DROP TABLE [dbo].[tblUserMenu]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblUserMenu](
	[UserMenuID] [bigint] IDENTITY(1,1) NOT NULL,
	[UserID] [bigint] NOT NULL,	
	[AppMenuID] [int] NOT NULL,
	[Status] [int] NOT NULL,	
	[RevokeStatus] [bit] NOT NULL,
	[DateRevoked] [datetime] NULL,	
	[DateCreated] [datetime] NOT NULL,
	[UserCreated] [bigint] NOT NULL,
	[DateUpdated] [datetime] NULL,
	[UserUpdated] [bigint] NULL,
 CONSTRAINT [PK_tblUserMenu] PRIMARY KEY CLUSTERED 
(
	[UserMenuID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblUserMenu] ADD  DEFAULT ((0)) FOR [RevokeStatus]
GO

ALTER TABLE [dbo].[tblUserMenu] ADD  DEFAULT ((1)) FOR [Status]
GO

ALTER TABLE [dbo].[tblUserMenu] ADD  DEFAULT (getdate()) FOR [DateCreated]
GO

ALTER TABLE [dbo].[tblUserMenu] ADD  DEFAULT ((0)) FOR [UserCreated]
GO

USE [ppjdb]
GO

ALTER TABLE [dbo].[tblUserMenu]  WITH CHECK ADD  CONSTRAINT [FK_tblUserMenu_AppMenuID] FOREIGN KEY([AppMenuID])
REFERENCES [dbo].[tblAppMenu] ([AppMenuID])
GO

ALTER TABLE [dbo].[tblUserMenu] CHECK CONSTRAINT [FK_tblUserMenu_AppMenuID]
GO

-------------------------------------------------------------------------------------------
--select * from [dbo].[tblUserMenu] (nolock)
-- insert set of records for firest "test" user
if not exists (select 1 from [dbo].[tblUserMenu] (nolock) where [UserID] = 1)
insert into [dbo].[tblUserMenu] (
	[UserID],	
	[AppMenuID],
	[Status]
)
select 1,AppMenuID,4
from dbo.tblAppMenu (nolock)	
go

-------------------------------------------------------------------------------------------
select * from dbo.tblUIMessage
if not exists (select 1 from dbo.tblUIMessage (nolock) where UIErrCode = 203)
insert into dbo.tblUIMessage (UIErrCode,MsgTier2,MsgTier3)
select 203,
	'Login failed please contact with PPJ administrator.',
	'Login failed please contact with PPJ administrator.'
go
if exists (select 1 from dbo.tblMsgOutput (nolock) where SPName='uspValidateLogin' and RetCode=-7)
update  dbo.tblMsgOutput set UIErrCode = 203
where SPName='uspValidateLogin' and RetCode=-7
go

-------------------------------------------------------------------------------------------
--select * from dbo.tblMsgOutput (nolock) where SPName = 'uspUserChangePassword'
if not exists (select 1 from dbo.tblMsgOutput (nolock) where SPName = 'uspUserChangePassword')
insert into dbo.tblMsgOutput (SPName,RetCode,MsgTier1,UIErrCode)
select 'uspUserChangePassword',-1, 'SQL update error',202
union all
select 'uspUserChangePassword',-2, 'parameter @biUserID is NULL or zero',202
union all
select 'uspUserChangePassword',-3, 'parameter @szPasswordOld is NULL or empty',202
union all
select 'uspUserChangePassword',-4, 'User not found',202
union all
select 'uspUserChangePassword',-5, 'User has revoke status',202
union all
select 'uspUserChangePassword',-6, 'Store has revoke status',202
union all
select 'uspUserChangePassword',-7, 'parameter @szPasswordOld does not match',202
union all
select 'uspUserChangePassword',-8, 'Email template not found',202
union all
select 'uspUserChangePassword',-9, 'error in [dbo].[uspSendEmail]',202
go

-----------------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[svc].[uspUserChangePassword]') AND type in (N'P', N'PC'))
DROP PROCEDURE [svc].[uspUserChangePassword]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*
  -----------------------------------------------------------------------
  File Name		:
  Description	:	Reset user's password on new or generated randomly value
	 and send email, if password was generated
  Author        :   Sergey Morozov
  Copyright     :   (c) 2012 PPJ
  Incept		:	08/08/2012
  -----------------------------------------------------------------------
  Description/Purpose:

  Output values:
	@iRetCode =  0, sucess,
	@iRetCode = -1, SQL update error, detail info recorded into dbo.tblSQLAudit table
	@iRetCode = -2, parameter @biUserID is NULL or zero
	@iRetCode = -3, Some of Password parameters is NULL or empty
	@iRetCode = -4, User not found
	@iRetCode = -5, User has revoke status
	@iRetCode = -6, Store has revoke status
	@iRetCode = -7, parameter @szPasswordOld does not match
	@iRetCode = -8, Email template not found
	@iRetCode = -9, error in [dbo].[uspSendEmail]	
  -----------------------------------------------------------------------
  Change Log:
	Author			Date         Change
  -----------------------------------------------------------------------
  Sergiy Morozov 08/08/2012		development
  
  -----------------------------------------------------------------------
  Sample Script
  -----------------------------------------------------------------------
	declare
		@biUserID [bigint]
		,@szPasswordOld [nvarchar] (128)
		,@szPassword [nvarchar] (128)
		,@szSecureQuestion [nvarchar] (512)
		,@szSecureAnswer [nvarchar] (512)
		,@iRetCode [int]
		,@szMsgTier1 [nvarchar] (2048)
		,@szMsgTier2 [nvarchar] (1024)

	select @biUserID = 1
		,@szPasswordOld = 'test1'
		,@szPassword = 'test'		
		,@szSecureQuestion ='test'
		,@szSecureAnswer ='test'

	exec [svc].[uspUserChangePassword]
		@biUserID
		,@szPasswordOld
		,@szPassword
		,@szSecureQuestion
		,@szSecureAnswer
		,@iRetCode output
		,@szMsgTier1 output   
		,@szMsgTier2 output  


	select 	
		@iRetCode as [RetCode] 
		,@szMsgTier1 as [RetMsg1]   
		,@szMsgTier2 as [RetMsg2]   		

*/

CREATE PROCEDURE [svc].[uspUserChangePassword]
	@biUserID [bigint]
	,@szPasswordOld [nvarchar] (128) = NULL
	,@szPassword [nvarchar] (128) = NULL
	,@szSecureQuestion [nvarchar] (512) = NULL
	,@szSecureAnswer [nvarchar] (512) = NULL		
    ,@iRetCode [int] output
    ,@szMsgTier1 [nvarchar] (2048) output   
    ,@szMsgTier2 [nvarchar] (1024) output  
AS
BEGIN

SET NOCOUNT ON;

DECLARE @iIsProcessComplete int
, @szProcessName VARCHAR(50)
, @ErrorMessage nvarchar(2048) 
, @ErrorNumber int
, @ErrorSeverity int
, @ErrorState int
, @ErrorProcedure nvarchar(2048)
, @ErrorLine int
, @TranCounter int
, @RowCount int
, @Msg nvarchar(4000)
, @iJobSetID int
, @szInsertedBy varchar(16)
, @szErrSubst [varchar] (100)

-- Get current stored proc name
SELECT @szProcessName = ISNULL(OBJECT_NAME(@@PROCID), 'uspUserChangePassword')

BEGIN TRY
-----------------------------------
--BEGIN WORK
-----------------------------------

declare @bRevokeStatus [bit],
	@szTmpPassword [nvarchar] (128),
	@biTmpUserID [bigint],
	@szTmpSecureQuestion [nvarchar] (512),
	@bStoreRevokeStatus [bit],
	@bResetPassword [bit],
	@szUserEmail [nvarchar] (512)	

select @iRetCode = 0
	,@szMsgTier1 =''
	,@szMsgTier2=''
	,@szErrSubst = ''
	,@bRevokeStatus = 1
	,@szTmpPassword = ''
	,@biTmpUserID = 0
	,@szSecureQuestion = ''
	,@bStoreRevokeStatus= 1

-- Validate parameters
if coalesce(@biUserID,'')=0
-- Parameter is NULL or zero
	set @iRetCode = -2
else if coalesce(@szPassword,'')<>'' and (coalesce(@szPasswordOld,'')='' or coalesce(@szSecureQuestion,'')='' or coalesce(@szSecureAnswer,'')='')
 begin
	set @iRetCode = -3
  end
else 	
 begin
	select 
		@bRevokeStatus = u.RevokeStatus,
		@szTmpPassword = u.[Password],
		@szTmpSecureQuestion = u.SecureQuestion,
		@bStoreRevokeStatus = s.RevokeStatus,
		@bResetPassword = ResetPassword,
		@szUserEmail = UserEmail
		from dbo.tblUser u (nolock) 
		left join dbo.tblStore s (nolock) on s.StoreID = u.StoreID
		where UserID = @biUserID
		
	if @@ROWCOUNT = 0
	 begin
-- no user found
		set @iRetCode = -4
	 end
	else if coalesce(@bRevokeStatus,0)=1
	 begin
-- user has Revoked status	 
		set @iRetCode = -5
	 end
	else if coalesce(@bStoreRevokeStatus,0)=1
	 begin
-- user has Revoked status	 
		set @iRetCode = -6
	 end
	else if (coalesce(@szPasswordOld,'')<>'' and @szTmpPassword <> @szPasswordOld COLLATE SQL_Latin1_General_CP1_CS_AS)
	 begin
-- password does not match	 
		select @iRetCode = -7
	 end
	else
	 begin
-- update password 
		if coalesce(@szPassword,'')=''
		 begin
-- generate password
			select @szPassword = dbo.fnGeneratePassword(8,0)
				,@szSecureQuestion = 'Randomly generated password'
				,@szSecureAnswer = @szPassword
				,@bResetPassword = 1
		 end
		else
		 begin
			set @bResetPassword = 0
		 end 

		-- do we have outer transaction?
		SET @TranCounter = @@TRANCOUNT;
		IF @TranCounter > 0
		  SAVE TRANSACTION uspUserChangePassword;
		else
		  BEGIN TRANSACTION;

		update dbo.tblUser set [Password] = @szPassword,
			SecureQuestion = @szSecureQuestion,
			SecureAnswer = @szSecureAnswer,
			ResetPassword = @bResetPassword,
			DateUpdated = GETDATE()
			where UserID = @biUserID

		-- commit transaction
		if @TranCounter = 0 -- no outer transaction
		 begin
		  COMMIT TRANSACTION;
		  set @TranCounter = NULL
		 end 

		if @bResetPassword = 1
		 begin
-- send email
			declare @szEmailTemplate [varchar] (max),
				@szSource [varchar](100),
				@szEmailSubject [nvarchar](512),
				@szEmailDetails [nvarchar](max),
				@szRecipientListAddresses [nvarchar](max),
				@szSQLQueryString [nvarchar] (max),
				@iTmpUserID [int],
				@iQResultWidth [int],
				@szEmailFormat [varchar] (10),
				@iTmpRetCode [int],
				@bRecordOnly [bit],
				@szDateTime [varchar] (30)

			declare @szYear [varchar] (4),
				@szDate1 [varchar] (30),
				@szDate2 [varchar] (30),
				@iPos [int]
			
			select @szEmailTemplate = [Template],
				@szEmailSubject = [Subject],
				@szEmailFormat = [EmailFormat] 
				from dbo.tblEmailTemplate (nolock) 
				where TemplateName = 'ChangePassword'
				and RevokeStatus = 0

			if coalesce(@szEmailTemplate,'')=''
			 begin
	-- email template not found
				select @iRetCode = -8		 
			 end
			else
			 begin
					
				select @szYear = CAST(year(getDate()) as [varchar] (4)),
					@szDate1 = convert(varchar, getdate(), 100),
					@iPos = charindex(@szYear,@szDate1)+5,
					@szDate2 = CONVERT(varchar,getdate(),101)+' at '+
					ltrim(SUBSTRING(@szDate1,@iPos,len(@szDate1)-@iPos+1))		 
					
				select @szEmailTemplate = replace(replace(@szEmailTemplate,'[pwd]',@szTmpPassword),'[dt]',@szDate2)

				select @szEmailDetails = @szEmailTemplate,
					@szSource = @szProcessName,
					@szRecipientListAddresses = @szUserEmail

				exec [dbo].[uspSendEmail]
					@szSource,
					@szEmailSubject,
					@szEmailDetails,
					@szRecipientListAddresses,
					@szSQLQueryString,
					@iTmpUserID,
					@iQResultWidth,
					@szEmailFormat,
					@iTmpRetCode output
				
				if @iTmpRetCode <> 0
				 begin
					select @iRetCode = -9
				 end
			 end	
		 end
	 end 
 end

if @iRetCode <> 0
 begin
	select @szMsgTier1=replace(t.MsgTier1,'{0}',''''+@szErrSubst+''''),
		@szMsgTier2=replace(u.MsgTier2,'{0}',''''+@szErrSubst+''''),
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode
 end

 RETURN 0
END TRY
BEGIN CATCH
-- If failed rollback
-- get extended error information
	select @ErrorNumber  = ERROR_NUMBER(), @ErrorSeverity  = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
	, @ErrorProcedure  = ERROR_PROCEDURE(), @ErrorLine  = ERROR_LINE(), @ErrorMessage  = ERROR_MESSAGE()
	set @Msg = '; ErrorNumber = ' + cast(@ErrorNumber as varchar(4))
	+ ', ErrorSeverity  = '       + cast(@ErrorSeverity as varchar(4))
	+ ', ErrorState = '                 + cast(@ErrorState as varchar(4))
	+ ', ErrorProcedure = '       + cast(@ErrorProcedure as varchar(128))
	+ ', ErrorLine = '                  + cast(@ErrorLine as varchar(4))
	+ ', ErrorMessage = '         + cast(@ErrorMessage as varchar(2048))

	IF @TranCounter is not null
	begin
	IF @TranCounter = 0 -- Transaction started in procedure.
	ROLLBACK TRANSACTION;
	ELSE
	IF XACT_STATE() <> -1 -- roll back to the savepoint
	ROLLBACK TRANSACTION uspUserChangePassword;
	end

	-- log error
	EXECUTE  dbo.uspRecordSQLAudit
	@biProcessID = -1
	,@iErrorNumber = @ErrorNumber
	,@iErrorSeverity = @ErrorSeverity
	,@iErrorState = @ErrorState
	,@szErrorProcedure = @ErrorProcedure
	,@iErrorLine = @ErrorLine
	,@szErrorMessage = @ErrorMessage
	,@szParameters = ''
	,@iInserted = @biTmpUserID

	select @iRetCode = -1 

	select @szMsgTier1=@ErrorMessage,
		@szMsgTier2=u.MsgTier2,
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode

RETURN -1
END CATCH

END
GO


-----------------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[svc].[uspUserChangeStatus]') AND type in (N'P', N'PC'))
DROP PROCEDURE [svc].[uspUserChangeStatus]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*
  -----------------------------------------------------------------------
  File Name		:
  Description	:	Reset user's RevokeStatus
  Author        :   Sergey Morozov
  Copyright     :   (c) 2012 PPJ
  Incept		:	08/08/2012
  -----------------------------------------------------------------------
  Description/Purpose:

  Output values:
	@iRetCode =  0, sucess,
	@iRetCode = -1, SQL update error, detail info recorded into dbo.tblSQLAudit table
	@iRetCode = -2, parameter @biUserID is NULL or zero
	@iRetCode = -3, User not found
	@iRetCode = -4, Store has revoke status
	@iRetCode = -5, User already has requested status
  -----------------------------------------------------------------------
  Change Log:
	Author			Date         Change
  -----------------------------------------------------------------------
  Sergiy Morozov 08/08/2012		development
  
  -----------------------------------------------------------------------
  Sample Script
  -----------------------------------------------------------------------
	declare
		@biUserID [bigint]
		,@bRevokeStatus [bit]
		,@iRetCode [int]
		,@szMsgTier1 [nvarchar] (2048)
		,@szMsgTier2 [nvarchar] (1024)

	select @biUserID = 1
		,@bRevokeStatus = 0

	exec [svc].[uspUserChangeStatus]
		@biUserID
		,@bRevokeStatus
		,@iRetCode output
		,@szMsgTier1 output   
		,@szMsgTier2 output  

	select 	
		@iRetCode as [RetCode] 
		,@szMsgTier1 as [RetMsg1]   
		,@szMsgTier2 as [RetMsg2]   		
select * from tbluser where userId = 1
*/

CREATE PROCEDURE [svc].[uspUserChangeStatus]
	@biUserID [bigint]
	,@bRevokeStatus [bit] = 1
    ,@iRetCode [int] output
    ,@szMsgTier1 [nvarchar] (2048) output   
    ,@szMsgTier2 [nvarchar] (1024) output  
AS
BEGIN

SET NOCOUNT ON;

DECLARE @iIsProcessComplete int
, @szProcessName VARCHAR(50)
, @ErrorMessage nvarchar(2048) 
, @ErrorNumber int
, @ErrorSeverity int
, @ErrorState int
, @ErrorProcedure nvarchar(2048)
, @ErrorLine int
, @TranCounter int
, @RowCount int
, @Msg nvarchar(4000)
, @iJobSetID int
, @szInsertedBy varchar(16)
, @szErrSubst [varchar] (100)

-- Get current stored proc name
SELECT @szProcessName = ISNULL(OBJECT_NAME(@@PROCID), 'uspUserChangeStatus')

BEGIN TRY
-----------------------------------
--BEGIN WORK
-----------------------------------

declare @bStoreRevokeStatus [bit],
	@bTmpRevokeStatus [bit]	

select @iRetCode = 0
	,@szMsgTier1 =''
	,@szMsgTier2=''
	,@szErrSubst = ''
	,@bRevokeStatus = coalesce(@bRevokeStatus,0)
	,@bStoreRevokeStatus= 1

-- Validate parameters
if coalesce(@biUserID,'')=0
-- Parameter is NULL or zero
	set @iRetCode = -2
 begin
	select 
		@bTmpRevokeStatus = u.RevokeStatus,
		@bStoreRevokeStatus = s.RevokeStatus
		from dbo.tblUser u (nolock) 
		left join dbo.tblStore s (nolock) on s.StoreID = u.StoreID
		where UserID = @biUserID
		
	if @@ROWCOUNT = 0
	 begin
-- no user found
		set @iRetCode = -3
	 end
	else if coalesce(@bStoreRevokeStatus,0)=1
	 begin
-- user has Revoked status	 
		set @iRetCode = -4
	 end
	else if @bTmpRevokeStatus=@bRevokeStatus
	 begin
-- user has requested status	 
		select @iRetCode = -5,
			@szErrSubst='RevokeStatus='+CAST(@bRevokeStatus as [varchar] (10))			
	 end
	else
	 begin
-- update User

		-- do we have outer transaction?
		SET @TranCounter = @@TRANCOUNT;
		IF @TranCounter > 0
		  SAVE TRANSACTION uspUserChangeStatus;
		else
		  BEGIN TRANSACTION;

		update dbo.tblUser set [RevokeStatus] = @bRevokeStatus,
			DateRevoked = case when @bRevokeStatus = 1 then GETDATE() else NULL end,
			DateUpdated = GETDATE()
			where UserID = @biUserID

		-- commit transaction
		if @TranCounter = 0 -- no outer transaction
		 begin
		  COMMIT TRANSACTION;
		  set @TranCounter = NULL
		 end 
	 end 
 end

if @iRetCode <> 0
 begin
	select @szMsgTier1=replace(t.MsgTier1,'{0}',''''+@szErrSubst+''''),
		@szMsgTier2=replace(u.MsgTier2,'{0}',''''+@szErrSubst+''''),
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode
 end

 RETURN 0
END TRY
BEGIN CATCH
-- If failed rollback
-- get extended error information
	select @ErrorNumber  = ERROR_NUMBER(), @ErrorSeverity  = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
	, @ErrorProcedure  = ERROR_PROCEDURE(), @ErrorLine  = ERROR_LINE(), @ErrorMessage  = ERROR_MESSAGE()
	set @Msg = '; ErrorNumber = ' + cast(@ErrorNumber as varchar(4))
	+ ', ErrorSeverity  = '       + cast(@ErrorSeverity as varchar(4))
	+ ', ErrorState = '                 + cast(@ErrorState as varchar(4))
	+ ', ErrorProcedure = '       + cast(@ErrorProcedure as varchar(128))
	+ ', ErrorLine = '                  + cast(@ErrorLine as varchar(4))
	+ ', ErrorMessage = '         + cast(@ErrorMessage as varchar(2048))

	IF @TranCounter is not null
	begin
	IF @TranCounter = 0 -- Transaction started in procedure.
	ROLLBACK TRANSACTION;
	ELSE
	IF XACT_STATE() <> -1 -- roll back to the savepoint
	ROLLBACK TRANSACTION uspUserChangeStatus;
	end

	-- log error
	EXECUTE  dbo.uspRecordSQLAudit
	@biProcessID = -1
	,@iErrorNumber = @ErrorNumber
	,@iErrorSeverity = @ErrorSeverity
	,@iErrorState = @ErrorState
	,@szErrorProcedure = @ErrorProcedure
	,@iErrorLine = @ErrorLine
	,@szErrorMessage = @ErrorMessage
	,@szParameters = ''
	,@iInserted = @biUserID

	select @iRetCode = -1 

	select @szMsgTier1=@ErrorMessage,
		@szMsgTier2=u.MsgTier2,
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode

RETURN -1
END CATCH

END
GO

-----------------------------------------------------------------------------------------------
if not exists (select 1 from dbo.tblUIMessage (nolock) where UIErrCode = 204)
insert into dbo.tblUIMessage (UIErrCode,MsgTier2,MsgTier3)
select 204,
	'Update status failed please contact with PPJ administrator.',
	'Update status failed please contact with PPJ administrator.'
go

-------------------------------------------------------------------------------------------
if not exists (select 1 from dbo.tblMsgOutput (nolock) where SPName = 'uspUserChangeStatus')
insert into dbo.tblMsgOutput (SPName,RetCode,MsgTier1,UIErrCode)
select 'uspUserChangeStatus',-1, 'SQL update error',202
union all
select 'uspUserChangeStatus',-2, 'parameter @biUserID is NULL or zero',202
union all
select 'uspUserChangeStatus',-3, 'User not found',202
union all
select 'uspUserChangeStatus',-4, 'Store has "revoke" status',202
union all
select 'uspUserChangeStatus',-5, 'User already has requested status',204
go

if (select COUNT(*) from dbo.tblAppMenu (nolock) where ParentID = 0)=1
 begin
  update dbo.tblAppMenu set ParentID = 0 where ParentID = 1
--  update dbo.tblAppMenu set ParentID = 2 where ParentID = 1
 end
 
 ------------------------------------------------------------------
 
if not exists (select 1 from dbo.tblMsgOutput (nolock) where SPName = 'uspUserGetMenuOptions')
insert into dbo.tblMsgOutput (SPName,RetCode,MsgTier1,UIErrCode)
select 'uspUserGetMenuOptions',-1, 'SQL update error',202
union all
select 'uspUserGetMenuOptions',-2, 'parameter @biUserID is NULL or zero',202
union all
select 'uspUserGetMenuOptions',-3, 'User not found',202
go

 ------------------------------------------------------------------
 
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[svc].[uspUserGetMenuOptions]') AND type in (N'P', N'PC'))
DROP PROCEDURE [svc].[uspUserGetMenuOptions]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*
  -----------------------------------------------------------------------
  File Name		:
  Description	:	Reset user's password on new or generated randomly value
	 and send email, if password was generated
  Author        :   Sergey Morozov
  Copyright     :   (c) 2012 PPJ
  Incept		:	08/08/2012
  -----------------------------------------------------------------------
  Description/Purpose:

  Output values:
	@iRetCode =  0, sucess,
	@iRetCode = -1, SQL update error, detail info recorded into dbo.tblSQLAudit table
	@iRetCode = -2, parameter @biUserID is NULL or zero
	@iRetCode = -3, User not found

  -----------------------------------------------------------------------
  Change Log:
	Author			Date         Change
  -----------------------------------------------------------------------
  Sergiy Morozov 08/08/2012		development
  
  -----------------------------------------------------------------------
  Sample Script
  -----------------------------------------------------------------------

	declare
		@biUserID [bigint]
		,@iRetCode [int]
		,@szMsgTier1 [nvarchar] (2048)
		,@szMsgTier2 [nvarchar] (1024)

	select @biUserID = 1

	exec [svc].[uspUserGetMenuOptions]
		@biUserID
		,@iRetCode output
		,@szMsgTier1 output   
		,@szMsgTier2 output  

	select 	
		@iRetCode as [RetCode] 
		,@szMsgTier1 as [RetMsg1]   
		,@szMsgTier2 as [RetMsg2]   		

*/

CREATE PROCEDURE [svc].[uspUserGetMenuOptions]
	@biUserID [bigint]
    ,@iRetCode [int] output
    ,@szMsgTier1 [nvarchar] (2048) output   
    ,@szMsgTier2 [nvarchar] (1024) output  
AS
BEGIN

SET NOCOUNT ON;

DECLARE @iIsProcessComplete int
, @szProcessName VARCHAR(50)
, @ErrorMessage nvarchar(2048) 
, @ErrorNumber int
, @ErrorSeverity int
, @ErrorState int
, @ErrorProcedure nvarchar(2048)
, @ErrorLine int
, @TranCounter int
, @RowCount int
, @Msg nvarchar(4000)
, @iJobSetID int
, @szInsertedBy varchar(16)
, @szErrSubst [varchar] (100)

-- Get current stored proc name
SELECT @szProcessName = ISNULL(OBJECT_NAME(@@PROCID), 'uspUserGetMenuOptions')

BEGIN TRY
-----------------------------------
--BEGIN WORK
-----------------------------------


select @iRetCode = 0
	,@szMsgTier1 =''
	,@szMsgTier2=''
	,@szErrSubst = ''

declare @tblUser table (UserID [bigint] not null,
	FirstName [nvarchar] (128) not null,
	LastName [nvarchar] (128) not null,
	UserEmail [nvarchar] (512) not null,
	ResetPassword [bit] not null,
	DateCreated [datetime] not null,
	DateUpdated [datetime] null,
	StoreID [bigint] not null default (0),
	StoreName [nvarchar] (512) not null default (''))

-- Validate parameters
if coalesce(@biUserID,'')=0
-- Parameter is NULL or zero
	set @iRetCode = -2
else 	
 begin
	insert into @tblUser (
		UserID,
		FirstName,
		LastName,
		UserEmail,
		ResetPassword,
		DateCreated,
		DateUpdated,
		StoreID,
		StoreName)
	select 
		u.UserID,
		u.FirstName,
		u.LastName,
		u.UserEmail,
		u.ResetPassword,
		u.DateCreated,
		u.DateUpdated,
		s.StoreID,
		s.StoreName
		from dbo.tblUser u (nolock) 
		left join dbo.tblStore s (nolock) on s.StoreID = u.StoreID
		where UserID = @biUserID
		
	if @@ROWCOUNT = 0
	 begin
-- no user found
		select @iRetCode = -3,
			@szErrSubst = CAST(@biUserID as [varchar] (10))
	 end
 end

if @iRetCode = 0
 begin
	
	select * from @tblUser

	;with t0 as (
	select am.AppMenuID,
		 am.MenuName,
		 am.ParentID,
		 vl.LookUpShortName
		from dbo.tblUserMenu um (nolock) 
		inner join dbo.tblAppMenu am (nolock) on um.AppMenuID = am.AppMenuID
		inner join dbo.vwLookUp vl (nolock) on vl.LookUpName = 'MenuStatus'
			and vl.LookUpSubType = um.[Status]
		where um.UserID = @biUserID
		and um.RevokeStatus = 0
		and am.RevokeStatus = 0
	), tt as (
	select AppMenuID,
			MenuName,
			LookUpShortName as UserRights,
			ParentID,
			ROW_NUMBER() OVER(ORDER BY AppMenuID) as RNumber
			from t0 where ParentID =0
		union all 
		select t.AppMenuID,
			t.MenuName,
			t.LookUpShortName as UserRights,
			t.ParentID,
			ROW_NUMBER() OVER(ORDER BY t.AppMenuID) as RNumber
			from t0 t 
--			inner join tt on tt.ParentID=t.ParentID
			where t.ParentID <>0
		)
	select * from tt	
				
		
		
 end
else
 begin
	select @szMsgTier1=replace(t.MsgTier1,'{0}',''''+@szErrSubst+''''),
		@szMsgTier2=replace(u.MsgTier2,'{0}',''''+@szErrSubst+''''),
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode
 end

 RETURN 0
END TRY
BEGIN CATCH
-- If failed rollback
-- get extended error information
	select @ErrorNumber  = ERROR_NUMBER(), @ErrorSeverity  = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
	, @ErrorProcedure  = ERROR_PROCEDURE(), @ErrorLine  = ERROR_LINE(), @ErrorMessage  = ERROR_MESSAGE()
	set @Msg = '; ErrorNumber = ' + cast(@ErrorNumber as varchar(4))
	+ ', ErrorSeverity  = '       + cast(@ErrorSeverity as varchar(4))
	+ ', ErrorState = '                 + cast(@ErrorState as varchar(4))
	+ ', ErrorProcedure = '       + cast(@ErrorProcedure as varchar(128))
	+ ', ErrorLine = '                  + cast(@ErrorLine as varchar(4))
	+ ', ErrorMessage = '         + cast(@ErrorMessage as varchar(2048))

	IF @TranCounter is not null
	begin
	IF @TranCounter = 0 -- Transaction started in procedure.
	ROLLBACK TRANSACTION;
	ELSE
	IF XACT_STATE() <> -1 -- roll back to the savepoint
	ROLLBACK TRANSACTION uspUserGetMenuOptions;
	end

	-- log error
	EXECUTE  dbo.uspRecordSQLAudit
	@biProcessID = -1
	,@iErrorNumber = @ErrorNumber
	,@iErrorSeverity = @ErrorSeverity
	,@iErrorState = @ErrorState
	,@szErrorProcedure = @ErrorProcedure
	,@iErrorLine = @ErrorLine
	,@szErrorMessage = @ErrorMessage
	,@szParameters = ''
	,@iInserted = @biUserID

	select @iRetCode = -1 

	select @szMsgTier1=@ErrorMessage,
		@szMsgTier2=u.MsgTier2,
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode

RETURN -1
END CATCH

END
GO

-------------------------------------------------------------------------